<?php


namespace Muyuxuan\mysql;
/**
 * 数据库类
 */
class MySql
{
    /**求距离
     * @param array|int[] $location 必要两个字段(long=经度,lat=纬度)
     * @param string $long 经度字段
     * @param string $lat 纬度字段
     * @param string $distance 表示距离字段
     * @param null $callback 回调函数(参数是生成的sql)
     * @return mixed|string
     */
    public static function Distance(array $location=['long'=>0,'lat'=>0],string $long='long',string $lat='lat',string $distance='distance',$callback=null){
        $str = "ST_Distance_Sphere(point(`{$long}`,`{$lat}`),point({$location['long']},{$location['lat']})) as {$distance}";
        if(empty($callback)){
            return $str;
        }else{
            return $callback($str);
        }
    }

    /**求排行
     * @param string $table_name 表名称
     * @param string $sort 排序字段
     * @param array $where 条件
     * @param array $hav 单独查询某排名的条件
     * @param string $field 查询的字段
     * @param string $rownum 排行显示字段
     * @param null $callback 回调函数
     * @param string $prefix 数据表前缀
     * @return mixed|string
     */
    public static function GetRanking(string $table_name,string $sort,array $where=[],array $hav=[],string $field='*',string $rownum='rownum',$callback=null,$prefix=''){
        if(!empty($prefix)){
            if(strpos($table_name,$prefix)===false){
                $table_name = $prefix.$table_name;
            }
        }
        $whereSql = "";
        if(!empty($where)){
            $whereSql .= " WHERE";
            foreach ($where as $key=>$value){
                if(is_array($value)){
                    $value = implode(',',$value);
                    $whereSql.= " {$key} in ({$value}) AND";
                }else{
                    $whereSql.= " {$key}{$value} AND";
                }
            }
            $whereSql = trim($whereSql,'AND');
        }
        $hasSql = "";
        if(!empty($hav)){
            $hasSql .= " WHERE";
            foreach ($hav as $key=>$value){
                if(is_array($value)){
                    $value = implode(',',$value);
                    $hasSql .= " {$key} in ({$value}) AND";
                }else{
                    $hasSql .= " {$key}={$value} AND";
                }
            }
            $hasSql = trim($hasSql,'AND');
        }

        if(empty($hasSql)){
            $sql = "SELECT t.$field, @$rownum := @$rownum + 1 AS $rownum FROM (SELECT @$rownum := 0) r, (SELECT * FROM $table_name $whereSql ORDER BY $sort) AS t";
        }else{
            $sql = "SELECT b.$field FROM (SELECT t.*, @$rownum := @$rownum + 1 AS $rownum FROM (SELECT @$rownum := 0) r,(SELECT * FROM $table_name $whereSql ORDER BY $sort) AS t) AS b $hasSql";
        }
        if(!empty($callback)){
            return $callback($sql);
        }else{
            return $sql;
        }
    }

    /**获取年纪
     * @param string $breithFile 生日字段
     * @param string $as 别名
     * @param string|null $callback 回调
     * @return mixed|string
     */
    public static function brithdateToAge(string $breithFile,string $as='age',string $callback=null){
        $str = "TIMESTAMPDIFF(YEAR,DATE({$breithFile}),CURDATE())  AS {$as}";
        if(!empty($callback)){
            return $callback($str);
        }
        return $str;
    }
}